package com.ops.service;

import com.ops.entity.Inspection;
import com.ops.repository.InspectionRepository;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

@Service
public class InspectionService {
    
    @Autowired
    private InspectionRepository inspectionRepository;
    
    /**
     * 导入Excel文件中的巡检数据
     * 
     * @param file 上传的Excel文件
     * @param reportUuid 报告UUID
     * @param hospitalId 医院ID
     * @param demandYear 需求年份
     * @return 成功导入的记录数
     * @throws Exception 导入过程中可能出现的异常
     */
    @Transactional
    public int importExcel(MultipartFile file, String reportUuid, String hospitalId, String demandYear) throws Exception {
        // 先删除已存在的数据，使用 deleteAll 确保事务同步
        List<Inspection> existingInspections = inspectionRepository.findByReportUuid(reportUuid);
        if (!existingInspections.isEmpty()) {
            inspectionRepository.deleteAll(existingInspections);
            inspectionRepository.flush(); // 强制刷新，确保删除操作立即生效
        }

        List<Inspection> inspectionsToSave = new ArrayList<>();

        try (Workbook workbook = new XSSFWorkbook(file.getInputStream())) {
            Sheet sheet = workbook.getSheetAt(0);
            
            for (Row row : sheet) {
                if (row.getRowNum() == 0) continue; // 跳过表头
                
                Inspection inspection = new Inspection();
                
                // 设置基本信息
                inspection.setHospitalId(hospitalId);
                inspection.setDemandYear(demandYear);
                inspection.setReportUuid(reportUuid);

                // 获取月份
                Integer month = getNumericCellValue(row.getCell(0)).intValue();
                inspection.setMonth(month);
                
                // 获取巡检人员
                inspection.setInspector(getCellValueAsString(row.getCell(1)));
                
                // 获取运维时间
                String opsTimeStr = getCellValueAsString(row.getCell(2));
                if (opsTimeStr != null && !opsTimeStr.isEmpty()) {
                    inspection.setOpsTime(parseDateTime(opsTimeStr));
                }
                
                // 获取线上巡检内容
                inspection.setOnlineContent(getCellValueAsString(row.getCell(3)));
                
                // 获取线下巡检内容
                inspection.setOfflineContent(getCellValueAsString(row.getCell(4)));
                
                // 检查是否存在相同月份的记录
//                Optional<Inspection> existingInspection = inspectionRepository
//                    .findByReportUuidAndHospitalIdAndDemandYearAndMonth(reportUuid, hospitalId, demandYear, month);
//
//                existingInspection.ifPresent(value -> inspection.setId(value.getId()));
                
                inspectionsToSave.add(inspection);
            }
        }
        
        // 批量保存新数据
        if (!inspectionsToSave.isEmpty()) {
            inspectionRepository.saveAll(inspectionsToSave);
            inspectionRepository.flush(); // 确保保存操作立即生效
        }
        
        return inspectionsToSave.size();
    }
    
    /**
     * 解析日期时间字符串为LocalDateTime对象
     * 
     * @param dateStr 日期时间字符串
     * @return 解析后的LocalDateTime对象，解析失败时返回null
     */
    private LocalDateTime parseDateTime(String dateStr) {
        try {
            if (dateStr.contains(".")) {
                dateStr = dateStr.substring(0, dateStr.indexOf("."));
            }
            
            DateTimeFormatter formatter;
            if (dateStr.length() > 16) {
                formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            } else {
                formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm");
            }
            return LocalDateTime.parse(dateStr, formatter);
        } catch (Exception e) {
            return null;
        }
    }
    
    private String getCellValueAsString(Cell cell) {
        if (cell == null) return null;
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getLocalDateTimeCellValue()
                            .format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
                }
                return String.format("%.0f", cell.getNumericCellValue());
            default:
                return null;
        }
    }
    
    private Double getNumericCellValue(Cell cell) {
        if (cell == null) return null;
        if (cell.getCellType() == CellType.NUMERIC) {
            return cell.getNumericCellValue();
        } else if (cell.getCellType() == CellType.STRING) {
            try {
                return Double.parseDouble(cell.getStringCellValue().trim());
            } catch (NumberFormatException e) {
                return null;
            }
        }
        return null;
    }
    
    /**
     * 获取指定条件的巡检记录列表
     * 
     * @param reportUuid 报告UUID
     * @param hospitalId 医院ID
     * @param demandYear 需求年份
     * @return 按月份升序排序的巡检记录列表
     */
    public List<Inspection> getInspections(String reportUuid,String hospitalId, String demandYear) {
        return inspectionRepository.findByReportUuidAndHospitalIdAndDemandYearOrderByMonthAsc(reportUuid, hospitalId, demandYear);
    }
}